/*
 * PKL 51 GUNUNG KIDUL
 * Subseksie DATA ENTRY + CAPI
 */

package pkl51.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import pkl51.database.DatabaseUtility;
import pkl51.entity.Blok2;
import pkl51.error.EntityException;
import pkl51.service.Blok2Dao;

/**
 *
 * @author thiolutfi
 */
public class Blok2DaoImpl implements Blok2Dao {

    private Connection connection;
    private final String insertBlok2 = "INSERT INTO blok2 VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE B2R1 = ?, B2R2 = ?, B2R3 = ?";
    private final String updateBlok2 = "UPDATE blok2 SET B2R1 = ?, B2R2 = ?, B2R3 = ? WHERE NKS = ?";
    private final String getBlok2ByNKS = "SELECT * FROM blok2 WHERE NKS = ?";

    @Override
    public void insertBlok2(Blok2 blok2) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(insertBlok2);

            statement.setString(1, blok2.getNKS());
            statement.setInt(2, blok2.getB2R1());
            statement.setInt(3, blok2.getB2R2());
            statement.setString(4, blok2.getB2R3());
            statement.setInt(5, blok2.getB2R1());
            statement.setInt(6, blok2.getB2R2());
            statement.setString(7, blok2.getB2R3());
            statement.executeUpdate();

            connection.commit();

        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            throw new EntityException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    @Override
    public void updateBlok2(Blok2 blok2) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(updateBlok2); //isikan statement yang akan dieksekusi

            statement.setInt(1, blok2.getB2R1());
            statement.setInt(2, blok2.getB2R2());
            statement.setString(3, blok2.getB2R3());
            statement.setString(4, blok2.getNKS());

            statement.executeUpdate();
            connection.commit();
        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    @Override
    public Blok2 getBlok2ByNKS(String NKS) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(getBlok2ByNKS);
            statement.setString(1, NKS);

            ResultSet result = statement.executeQuery();
            Blok2 blok2 = null;
            if (result.next()) {
                blok2 = new Blok2();
                blok2.setB2R1(result.getInt("B2R1"));
                blok2.setB2R2(result.getInt("B2R2"));
                blok2.setB2R3(result.getString("B2R3"));
            } else {
                throw new EntityException("Hasil untuk NKS: "+NKS+" tidak ditemukan");
            }
            connection.commit();
            return blok2;
        } catch (SQLException exception) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, exception);
            throw new EntityException(exception.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException exception) {
                    Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, exception);
                }
            }
        }
    }

}
